package com.dao.front.Impl;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.dao.BaseDao;
import com.dao.front.ISingerDaoFront;

@Repository
public class SingerDaoFrontImpl extends BaseDao implements ISingerDaoFront{

	@Override
	public List<Map<String, Object>> list() {
		String sql="select si.singerId,si.singerName,si.singerPhoto, "
				+ " si.seName,si.state,si.address,si.`work`,si.birthday, "
				+ " mu.musicId,mu.musicName,sp.specialId,sp.specialName, "
				+ " mu.timeLength,sp.specialPhoto,sp.issueDate,m.mvId,m.mvPhoto,m.mvMp4,m.mvName "
				+ " from singer as si "
				+ " left join music as mu "
				+ " on si.singerId=mu.singerId "
				+ " left join special as sp "
				+ " on mu.specialId=sp.specialId "
				+ " left join mv as m "
				+ " on si.singerId=m.singerId  "
				+ " group by si.singerId"
				+ " limit 0,10";
		return executeQuery(sql, null);
	}

	@Override
	public List<Map<String, Object>> querySingerType() {
		String sql ="select singerTypeId,singerTypeName from singertype";
		return executeQuery(sql, null);
	}

	@Override
	public List<Map<String, Object>> querySingerTypeId(int singerTypeId) {
		String sql ="select si.singerId,si.singerName,si.singerPhoto,si.seName,"
				+ " si.state,si.address,si.`work`,si.birthday,mu.musicId,mu.musicName,"
				+ " sp.specialId,sp.specialName,mu.timeLength,sp.specialPhoto,"
				+ " sp.issueDate,m.mvId,m.mvPhoto,m.mvMp4,m.mvName,"
				+ " sit.singerTypeId,sit.singerTypeName "
				+ " from singer as si left "
				+ " join music as mu on si.singerId=mu.singerId "
				+ " left join special as sp on mu.specialId=sp.specialId "
				+ " left join mv as m on si.singerId=m.singerId "
				+ " left join singertype as sit on si.singerTypeId=sit.singerTypeId "
				+ " where sit.singerTypeId=? group by si.singerId";
		Object[] params={singerTypeId};
		return executeQuery(sql, params);
	}

	@Override
	public List<Map<String, Object>> listSingerMusic(Integer singerId) {
		String sql ="select si.singerId,si.singerName,si.singerPhoto,si.seName,"
				+ " si.state,si.address,si.`work`,si.birthday,mu.musicId,mu.musicName,"
				+ " mu.musicMp3,mu.timeLength,sp.specialId,sp.specialName"
				+ " from singer as si "
				+ " left join music as mu "
				+ " on si.singerId=mu.singerId "
				+ " left join special as sp"
				+ " on mu.specialId=sp.specialId"
				+ " where si.singerId=?";
		Object[] params={singerId};
		return executeQuery(sql, params);
	}
	

	@Override
	public List<Map<String, Object>> listSingerSpecial(Integer singerId) {
		String sql="select si.singerId,si.singerName,si.singerPhoto,si.seName,"
				+ " si.state,si.address,si.`work`,si.birthday,sp.specialId,sp.specialName,"
				+ " sp.specialPhoto,sp.issueDate,mu.musicId "
				+ " from singer as si "
				+ " left join special as sp "
				+ " on si.singerId=sp.singerId "
				+ " left join music as mu"
				+ " on mu.specialId=sp.specialId"
				+ " where si.singerId=?"
				+ " group by sp.specialId";
			
		Object[] params={singerId};
		return executeQuery(sql, params);
	}
	
	@Override
	public List<Map<String, Object>> listSingerMv(Integer singerId) {
		String sql ="select si.singerId,si.singerName,si.singerPhoto,si.seName"
				+ " ,si.state,si.address,si.`work`,si.birthday,m.mvId,m.mvPhoto,m.mvMp4,m.mvName "
				+ " from singer as si "
				+ " left join mv as m "
				+ " on si.singerId=m.singerId where si.singerId=?";
		Object[] params={singerId};
		return executeQuery(sql, params);
	}

	@Override
	public Map<String, Object> mapSinger(Integer singerId) {
		String sql="select singerId,singerName,singerPhoto,seName,state,address,`work`,birthday from singer where singerId=?";
		Object[] params={singerId};
		return executeQueryMap(sql, params);
	}

	@Override
	public Map<String, Object> mapMusic(Integer singerId) {
		String sql ="select count(mu.musicId) as c from singer as si "
				+ " left join music as mu "
				+ " on si.singerId=mu.singerId where si.singerId=?";
		Object[] params={singerId};
		return executeQueryMap(sql, params);
	}

	@Override
	public Map<String, Object> mapSpecial(Integer singerId) {
		String sql ="select count(sp.singerId) as c from singer as si "
				+ " left join special as sp "
				+ " on si.singerId=sp.singerId where si.singerId=?";
		Object[] params={singerId};
		return executeQueryMap(sql, params);
	}

	@Override
	public Map<String, Object> mapMv(Integer singerId) {
		String sql ="select count(m.singerId) as c from singer as si "
				+ " left join mv as m "
				+ " on si.singerId=m.singerId where si.singerId=?";
		Object[] params={singerId};
		return executeQueryMap(sql, params);
	}

}
